/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import DAO.util.ConectaBanco;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import model.Login;
import org.apache.commons.mail.EmailException;
import org.apache.commons.mail.SimpleEmail;

/**
 *
 * @author Alex
 */
public class LoginDAO {

    public boolean criar(Login login) {
        try {
            Connection conexao = ConectaBanco.getConexao();

            String sql = "insert into login(usuario, senha,status) values (?,?,1)";
            // seta os parametros no sql
            PreparedStatement pstmt = conexao.prepareCall(sql);

            pstmt.setString(1, login.getUsuario());
            pstmt.setString(2, login.getSenha());

            System.out.println(sql);
            pstmt.execute();

            conexao.close();
            return true;

        } catch (SQLException sql) {

            return false;

        }

    }

    public ArrayList<Login> buscarTodas(String nome) throws ClassNotFoundException {
        ArrayList<Login> logins = new ArrayList<Login>();
        Connection conexao = null;
        try {
            conexao = ConectaBanco.getConexao();
            String sql = "select * from login where username ilike ? and status = 1";

            PreparedStatement pst = conexao.prepareStatement(sql);

            pst.setString(1, nome + "%");
            ResultSet rs = pst.executeQuery();

            while (rs.next()) {
                Login login = new Login();
                login.setId(rs.getInt("id"));
                login.setUsuario(rs.getString("username"));
                login.setSenha(rs.getString("senha"));

                logins.add(login);
            }
            conexao.close();
            return logins;

        } catch (SQLException sql) {
            return null;
        }

    }

    public Login buscarLoginByCodigo(int codigo) throws ClassNotFoundException, SQLException {
        Connection conexao = ConectaBanco.getConexao();
        PreparedStatement sql = conexao.prepareStatement("select * from login where id=? ");
        sql.setInt(1, codigo);
        ResultSet resultado = sql.executeQuery();
        Login login = new Login();
        while (resultado.next()) {
            login.setId(resultado.getInt("id"));
            login.setUsuario(resultado.getString("usuario"));
            login.setSenha(resultado.getString("senha"));
            login.setStatus(resultado.getInt("status"));

        }
        return login;
    }

    public boolean atualizar(Login login) {
        try {
            Connection conexao = ConectaBanco.getConexao();

            String sql = "update login set username=?, senha=? where id=?";

            PreparedStatement pstmt = conexao.prepareCall(sql);
            pstmt.setString(1, login.getUsuario());
            pstmt.setString(2, login.getSenha());
            pstmt.setInt(3, login.getId());

            System.out.println(sql);
            pstmt.execute();
            //out.println("Cadastro realizado com sucesso");            
            conexao.close();
            return true;
        } catch (SQLException sql) {
            sql.printStackTrace();
            return false;

        }
    }

    public boolean marcarInativo(int id) {
        try {
            Connection conexao = ConectaBanco.getConexao();

            String sql = "update login set status='inativo' where id=?";

            PreparedStatement pstmt = conexao.prepareCall(sql);

            pstmt.setInt(1, id);

            pstmt.execute();
            //out.println("Cadastro realizado com sucesso");            
            conexao.close();
            return true;
        } catch (SQLException sql) {
            sql.printStackTrace();
            return false;

        }
    }

    public boolean apagar(int tosco) {

        try {

            Connection conexao = ConectaBanco.getConexao();

            String sql = "delete from login where id = ?";
            PreparedStatement pstmt = conexao.prepareCall(sql);

            pstmt.setInt(1, tosco);

            pstmt.execute();

            conexao.close();
            return true;
        } catch (Exception ex) {

            ex.printStackTrace();
            return false;
        }

    }

    public Login login(String userName, String senha) throws SQLException, Exception {
        Connection conexao = ConectaBanco.getConexao();
        PreparedStatement sql = conexao.prepareStatement("select * from login where usuario =? and senha=?");
        sql.setString(1, userName);
        sql.setString(2, senha);
        Login login = new Login();

        ResultSet resultado = sql.executeQuery();

        resultado.next();

        login.setId(resultado.getInt("id"));
        login.setUsuario(resultado.getString("usuario"));
        login.setSenha(resultado.getString("senha"));
        login.setStatus(resultado.getInt("status"));

        conexao.close();
        return login;

    }

    public int idLogin(String userName, String senha) throws SQLException {
        Connection conexao = ConectaBanco.getConexao();
        PreparedStatement sql = conexao.prepareStatement("select id from login where username =? and senha=?");
        sql.setString(1, userName);
        sql.setString(2, senha);
        ResultSet resultado = sql.executeQuery();
        resultado.next();
        int id = resultado.getInt("id");

        return id;

    }

    

}
